package org.sump.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.sump.UMProvider;
import org.sump.data.Grant;
import org.sump.data.HasChildren;
import org.sump.data.HasGrants;
import org.sump.data.HasParent;
import org.sump.data.Permission;
import org.sump.data.Resource;
import org.sump.data.ResourceType;
import org.sump.data.Subject;
import org.sump.util.UMProviderAdapter;

import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;

public class MySQLUMProvider extends UMProviderAdapter implements UMProvider, ContextKeys {

	public static final String USERS_QUERY = "select * from %1s";
	public static final String USERS_TABLE_GRANTS_QUERY = "select * from mysql.tables_priv where User=?";
	public static final String USERS_DATABASE_GRANTS_QUERY = "select * from mysql.db where User=?";
	public static final String USERS_COLUMN_GRANTS_QUERY = "select * from mysql.columns_priv where User=?";
	public static final String USER_QUERY = "select * from %1s where User=?";
	public static final String DATABASE_QUERY = "show databases";
	public static final String TABLES_QUERY5 = "show full tables from %1s where Table_Type like \"%%TABLE%%\"";
	public static final String TABLES_QUERY4 = "show tables from %1s";
	public static final String COLUMNS_QUERY = "show columns from %1s from %2s";
	public static final String VIEW_QUERY5 = "show full tables from %1s where Table_Type like \"%%VIEW%%\"";
	public static final String PROCEDURES_QUERY5 = "show procedure status where db=\"%1s\"";
	public static final String FUNCTION_QUERY5 = "show function status where db=\"%1s\"";

	public static final String CREATE_USER_SQL4 = "INSERT INTO mysql.user (Host,User,Password) VALUES('%%','%1s',PASSWORD('some_pass'));";
//	public static final String CREATE_USER_SQL5 = "create user '%1s'@'%%';";
	public static final String CREATE_USER_SQL5 = "create user %s";
	public static final String REMOVE_USER_SQL4 = "delete from mysql.user where User='%1s';";
	public static final String REMOVE_USER_SQL5 = "drop user '%1s'@'%%';";
	public static final String REMOVE_GRANTS_STEP1 = "REVOKE ALL PRIVILEGES ON *.* FROM '%s'@'*'";
	public static final String REMOVE_GRANTS_STEP2 = "REVOKE GRANT OPTION ON *.* FROM '%s'@'*'";

	public static final String GRANT_SERVER = "GRANT %s on *.* TO '%s'";
	public static final String GRANT_DATABASE = "GRANT %s on %s.* TO '%s'@'*' ";
	public static final String GRANT_COLUMN = "GRANT %s (%s) on %s.%s TO '%s'@'*'";
	public static final String GRANT_TABLE = "GRANT %s on %s.%s TO '%s'@'*' ";

	public static final String REMOVE_GRANT_SERVER = "REVOKE %s on *.* FROM '%s'";
	public static final String REMOVE_GRANT_DATABASE = "REVOKE %s on %s.* FROM '%s'@'*'";
	public static final String REMOVE_GRANT_COLUMN = "REVOKE %s (%s) on %s.%s FROM '%s'@'*'";
	public static final String REMOVE_GRANT_TABLE = "REVOKE %s on %s.%s FROM '%s'@'*'";

	public static final String FLUSH_PRIVILEGES = "FLUSH PRIVILEGES;";

	public static final String DB_NAME_FIELD = "Db";
	public static final String TABLE_NAME_FIELD = "Table_name";
	public static final String COLUMN_NAME_FIELD = "Column_name";
	public static final String TABLE_PRIV_FIELD = "Table_priv";
	public static final String COLUMN_PRIV_FIELD = "Column_priv";
	
	
	// private Logger logger = Logger.getLogger(MySQLUMProvider.class);
	private String userTableName = "mysql.user";
	private MySQLVersion version;
	private DataSource ds = null;
	
	public MySQLUMProvider(MySQLVersion version) {
		this.version = version;
	}
	
	protected DataSource getDataSource() {
		if(ds == null) {
			ApplicationContext factory = ContextFactory.getInstance();
			ds = (DataSource)factory.getBean(DATA_SOURCE);
		}
		return ds;
	}
	
	private class SubjectRowMapper implements RowMapper {
		private MySQLResource server;
		
		public SubjectRowMapper() {
			super();
			Set<Resource> resources = getResources();
			this.server = (MySQLResource) resources.iterator().next();
		}

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			final String userName = rs.getString("User");
			MySQLSubject subject = new MySQLSubject(userName);
			ResourceType resourceType = server.getResourceType();
			Set<Grant> grants = new HashSet<Grant>();
			for(Permission permission : resourceType.getPermissions()) {
				MySQLPermission mySQLPermission = (MySQLPermission) permission;
				if(mySQLPermission.supportVersion(version)) {
					String fieldName = getPermissionFieldName(permission);
					String value = rs.getString(fieldName).toUpperCase();
					if("Y".equals(value)) {
						grants.add(new MySQLGrant(subject, server, permission));
					}
				}
			}
			loadDatabaseGrants(subject, grants);
			loadTableGrants(subject, grants);
			loadColumnGrants(subject, grants);
			subject.setGrants(grants);
			return subject;
		}
		
		private void loadDatabaseGrants(Subject subject, Collection<Grant> grants) {
			String query = USERS_DATABASE_GRANTS_QUERY;
			loadGrants(query, subject, grants, new DatabaseGrantsRowMapper((MySQLSubject) subject, server, grants));
		}
		
		private void loadTableGrants(Subject subject, Collection<Grant> grants) {
			String query = String.format(USERS_TABLE_GRANTS_QUERY, userTableName);
			loadGrants(query, subject, grants, new TableGrantsRowMapper((MySQLSubject) subject, server, grants));
		}

		private void loadColumnGrants(Subject subject, Collection<Grant> grants) {
			String query = String.format(USERS_COLUMN_GRANTS_QUERY, userTableName);
			loadGrants(query, subject, grants, new ColumnGrantsRowMapper((MySQLSubject) subject, server, grants));
		}
		
		private void loadGrants(String query, Subject subject, Collection<Grant> grants, RowMapper grantsRowMapper) {
			createJdbcTemplate().query(query, new Object[]{subject.getName()}, new RowMapperResultSetExtractor(grantsRowMapper));
		}
		
	}

	private abstract class AbstractGrantsRowMapper implements RowMapper {
		protected MySQLResource server;
		protected MySQLSubject subject;
		protected Collection<Grant> grants;

		private AbstractGrantsRowMapper(MySQLSubject subject, MySQLResource server, Collection<Grant> grants) {
			super();
			this.subject = subject;
			this.server = server;
			this.grants = grants;
		}
	}
	
	private class TableGrantsRowMapper extends AbstractGrantsRowMapper {

		private TableGrantsRowMapper(MySQLSubject subject, MySQLResource server, Collection<Grant> grants) {
			super(subject, server, grants);
		}


		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			String dbName = rs.getString(DB_NAME_FIELD);
			MySQLResource db = (MySQLResource) server.getChild(dbName, MySQLResourceType.DATABASE);
			String tableName = rs.getString(TABLE_NAME_FIELD);
			MySQLResource table = (MySQLResource) db.getChild(tableName, MySQLResourceType.TABLE);
			String priv = rs.getString(TABLE_PRIV_FIELD);
			String[] tokens = priv.split(",");
			for(String token : tokens) {
				token = token.trim();
				if(token.length() > 0) {
					MySQLPermission permission = MySQLPermission.convertFromDBToken(token);
					Grant grant = new MySQLGrant(subject, table, permission);
					grants.add(grant);
				}
			}
			return null;
		}
	}
	
	private class ColumnGrantsRowMapper extends AbstractGrantsRowMapper {

		private ColumnGrantsRowMapper(MySQLSubject subject, MySQLResource server, Collection<Grant> grants) {
			super(subject, server, grants);
		}


		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			String dbName = rs.getString(DB_NAME_FIELD);
			MySQLResource db = (MySQLResource) server.getChild(dbName, MySQLResourceType.DATABASE);
			
			String tableName = rs.getString(TABLE_NAME_FIELD);
			MySQLResource table = (MySQLResource) db.getChild(tableName, MySQLResourceType.TABLE);

			String columnName = rs.getString(COLUMN_NAME_FIELD);
			MySQLResource column = (MySQLResource) table.getChild(columnName, MySQLResourceType.COLUMN);

			String priv = rs.getString(COLUMN_PRIV_FIELD);
			String[] tokens = priv.split(",");
			for(String token : tokens) {
				token = token.trim();
				if(token.length() > 0) {
					MySQLPermission permission = MySQLPermission.convertFromDBToken(token);
					Grant grant = new MySQLGrant(subject, column, permission);
					grants.add(grant);
				}
			}
			return null;
		}
	}

	private class DatabaseGrantsRowMapper extends AbstractGrantsRowMapper {

		private DatabaseGrantsRowMapper(MySQLSubject subject, MySQLResource server, Collection<Grant> grants) {
			super(subject, server, grants);
		}


		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			String dbName = rs.getString(DB_NAME_FIELD);
			MySQLResource db = (MySQLResource) server.getChild(dbName, MySQLResourceType.DATABASE);
			for(Permission permission : db.getResourceType().getPermissions()) {
				MySQLPermission mySQLPermission = (MySQLPermission) permission;
				if(mySQLPermission.supportVersion(version)) {
					String fieldName = getPermissionFieldName(permission);
					String value = rs.getString(fieldName);
					if("Y".equals(value)) {
						Grant grant = new MySQLGrant(subject, db, permission);
						grants.add(grant);
					}
				}
			}
			return null;
		}
	}
	
	@Override
	public Set<Subject> getSubjects() {
		RowMapper subjectRowMapper = new SubjectRowMapper();
		String query = String.format(USERS_QUERY, userTableName);
		List<Subject> lst = (List<Subject>) createJdbcTemplate().query(query, new RowMapperResultSetExtractor(subjectRowMapper));
		Set<Subject> res = new HashSet<Subject>(lst); 
		return res;
	}


	protected String getPermissionFieldName(Permission permission) {
		return permission.getName() + "_priv";
	}

	@Override
	public Subject getSubjectByName(String userName) {
		RowMapper subjectRowMapper = new SubjectRowMapper();
		String query = String.format(USER_QUERY, userTableName);
		List<Subject> lst = (List<Subject>) createJdbcTemplate().query(query, new Object[]{userName}, new RowMapperResultSetExtractor(subjectRowMapper));
		//assert lst.size() > 1;
		return lst.isEmpty() ? null : lst.get(0);
	}

	private class DatabaseRowMapper implements RowMapper {
		private final ResourceType resourceType;
		private int colIdx;
		

		public DatabaseRowMapper(ResourceType resourceType, int colIdx) {
			super();
			this.resourceType = resourceType;
			this.colIdx = colIdx;
		}


		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			final String name = rs.getString(colIdx);
			return new MySQLResource(name, resourceType);
		}
	}

	@Override
	public Set<Resource> getResources() {
		DataSource dataSource = getDataSource();
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		MySQLResource server = new MySQLResource(MySQLResourceType.SERVER.getName(), MySQLResourceType.SERVER);
		
		String query = DATABASE_QUERY;
		List<Resource> dataBases = (List<Resource>) jdbcTemplate.query(query, new RowMapperResultSetExtractor(new DatabaseRowMapper(MySQLResourceType.DATABASE, 1)));
		
		server.setChildren(dataBases);
		Set<Resource> servers = new HashSet<Resource>();
		servers.add(server);
		
		for(Resource db : dataBases) {
			String dbName = db.getName();
			List<Resource> children = new ArrayList<Resource>();
			
			String tablesQuery = version == MySQLVersion.VERSION_4XX ? TABLES_QUERY4 : TABLES_QUERY5;
			List<Resource> tables = getDataBaseChildren(dataSource, String.format(tablesQuery, dbName), MySQLResourceType.TABLE, 1);			
			children.addAll(tables);
			for(Resource table : tables) {				
				String tableName = table.getName();
				List<Resource> columns = getDataBaseChildren(dataSource, String.format(COLUMNS_QUERY, tableName, dbName), MySQLResourceType.COLUMN, 1);
				((MySQLResource)table).setChildren(columns);
			}
			
			if(version == MySQLVersion.VERSION_5XX) {
				// There is no views in MySQL 4.XX
				List<Resource> views = getDataBaseChildren(dataSource, String.format(VIEW_QUERY5, dbName), MySQLResourceType.VIEW, 1);			
				children.addAll(views);

				List<Resource> procs = getDataBaseChildren(dataSource, String.format(PROCEDURES_QUERY5, dbName), MySQLResourceType.PROCEDURE, 2);			
				children.addAll(procs);
				
				List<Resource> functions = getDataBaseChildren(dataSource, String.format(FUNCTION_QUERY5, dbName), MySQLResourceType.FUNCTION, 2);			
				children.addAll(functions);
			}
			

			MySQLResource dbr = (MySQLResource) db;
			dbr.setChildren(children);
		}
		return servers;
	}
	
	private List<Resource> getDataBaseChildren(DataSource dataSource, String query, ResourceType resourceType, int colIdx) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		List<Resource> tables = (List<Resource>) jdbcTemplate.query(query, new RowMapperResultSetExtractor(new DatabaseRowMapper(resourceType, colIdx)));
		return tables; 
	}
	
	@Override
	public Set<ResourceType> getResourceTypes() {
		return new TreeSet<ResourceType>(Arrays.asList(MySQLResourceType.values()));
	}

	@Override
	public void addSubject(Subject subject) {
		String sql = version == MySQLVersion.VERSION_4XX ? CREATE_USER_SQL4 : CREATE_USER_SQL5;
		executeAndFlushPrivileges(String.format(sql, subject.getName()));
	}
	
	protected void executeAndFlushPrivileges(String sql) {
		JdbcTemplate jdbcTemplate = createJdbcTemplate();
		jdbcTemplate.execute(sql);
		jdbcTemplate.execute(FLUSH_PRIVILEGES);
	}
	
	private JdbcTemplate jdbcTemplate;
	protected JdbcTemplate createJdbcTemplate() {
		if(jdbcTemplate == null) {
			DataSource dataSource = getDataSource();
			jdbcTemplate = new JdbcTemplate(dataSource);
		}
		return jdbcTemplate;
	}

	@Override
	public void removeSubject(Subject subject) {
		String sql = String.format(REMOVE_GRANTS_STEP1, subject.getName());
		JdbcTemplate jdbcTemplate = executeAndSuppressErrorCode(sql, 1141);
		jdbcTemplate.execute(FLUSH_PRIVILEGES);

		sql = String.format(REMOVE_GRANTS_STEP2, subject.getName());
		jdbcTemplate = executeAndSuppressErrorCode(sql, 1141);
		jdbcTemplate.execute(FLUSH_PRIVILEGES);
		
		sql = version == MySQLVersion.VERSION_4XX ? REMOVE_USER_SQL4 : REMOVE_USER_SQL5;
		sql = REMOVE_USER_SQL4;
		executeAndFlushPrivileges(String.format(sql, subject.getName()));
	}
	
	protected JdbcTemplate executeAndSuppressErrorCode(String sql, int errorCode) {
		JdbcTemplate res = null;
		try {
			(res = createJdbcTemplate()).execute(sql);
		} catch (BadSqlGrammarException bge) {
			boolean throwAgain = true;
			if(bge.getSQLException() instanceof MySQLSyntaxErrorException) {
				MySQLSyntaxErrorException mySQLSyntaxErrorException = (MySQLSyntaxErrorException) bge.getSQLException();
				throwAgain  = mySQLSyntaxErrorException.getErrorCode() != errorCode;
			}
			if(throwAgain) {
				throw bge;
			}
		}
		return res;
	}

	@Override
	public void grant(HasGrants subject, Resource resource,
			Permission permission) {
		if(resource.getResourceType() == MySQLResourceType.COLUMN) {
			grantColumn(subject, resource, (MySQLPermission) permission);
		} else if(resource.getResourceType() == MySQLResourceType.TABLE) {
			grantTable(subject, resource, (MySQLPermission) permission);
		} else if(resource.getResourceType() == MySQLResourceType.DATABASE) {
			grantDatabase(subject, resource, (MySQLPermission) permission);
		} else if(resource.getResourceType() == MySQLResourceType.SERVER) {
			grantServer(subject, resource, (MySQLPermission) permission);
		}
	}

	protected void grantServer(HasGrants subject, Resource server,
			MySQLPermission permission) {
		String sql = String.format(GRANT_SERVER, permission.getPrivilegeName(), subject);
		executeAndFlushPrivileges(sql);
	}

	protected void grantDatabase(HasGrants subject, Resource database,
			MySQLPermission permission) {
		String sql = String.format(GRANT_DATABASE, permission.getPrivilegeName(), database, subject);
		executeAndFlushPrivileges(sql);
	}
			
	protected void grantColumn(HasGrants subject, Resource column,
			MySQLPermission permission) {
		HasParent hasParent = (HasParent) column;
		HasParent table = (HasParent) hasParent.getParent();
		HasParent database = (HasParent) table.getParent();
		String sql = String.format(GRANT_COLUMN, permission.getPrivilegeName(), hasParent, database, table, subject);
		executeAndFlushPrivileges(sql);
	}

	protected void grantTable(HasGrants subject, Resource table,
			MySQLPermission permission) {
		HasParent hasParent = (HasParent) table;
		HasChildren database = hasParent.getParent();
		String sql = String.format(GRANT_TABLE, permission.getPrivilegeName(), database, table, subject);
		executeAndFlushPrivileges(sql);
	}

	@Override
	public void removeGrant(HasGrants subject, Resource resource,
			Permission permission) {
		if(resource.getResourceType() == MySQLResourceType.COLUMN) {
			removeGrantColumn(subject, resource, (MySQLPermission) permission);
		} else if(resource.getResourceType() == MySQLResourceType.TABLE) {
			removeGrantTable(subject, resource, (MySQLPermission) permission);
		} else if(resource.getResourceType() == MySQLResourceType.DATABASE) {
			removeGrantDatabase(subject, resource, (MySQLPermission) permission);
		} else if(resource.getResourceType() == MySQLResourceType.SERVER) {
			removeGrantServer(subject, resource, (MySQLPermission) permission);
		}
	}
	
	

	protected void removeGrantServer(HasGrants subject, Resource server,
			MySQLPermission permission) {
		String sql = String.format(REMOVE_GRANT_SERVER, permission.getPrivilegeName(), subject);
		executeAndFlushPrivileges(sql);
	}
			
	protected void removeGrantDatabase(HasGrants subject, Resource database,
			MySQLPermission permission) {
		String sql = String.format(REMOVE_GRANT_DATABASE, permission.getPrivilegeName(), database, subject);
		executeAndFlushPrivileges(sql);
	}

	protected void removeGrantColumn(HasGrants subject, Resource column,
			MySQLPermission permission) {
		HasParent hasParent = (HasParent) column;
		HasParent table = (HasParent) hasParent.getParent();
		HasChildren database = table.getParent();
		String sql = String.format(REMOVE_GRANT_COLUMN, permission.getPrivilegeName(), hasParent, database, table, subject);
		executeAndFlushPrivileges(sql);
	}

	protected void removeGrantTable(HasGrants subject, Resource table,
			MySQLPermission permission) {
		HasParent hasParent = (HasParent) table;
		HasChildren database = hasParent.getParent();
		String sql = String.format(REMOVE_GRANT_TABLE, permission.getPrivilegeName(), database, table, subject);
		executeAndFlushPrivileges(sql);
	}
}
